package com.sm.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sm.domain.Score;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author admin
 * @since 2022-06-07
 */
public interface ScoreMapper extends BaseMapper<Score> {

    @Select("SELECT t.*,s.name stuName,s.stuNo stuNo,c.courseName cName FROM t_score t LEFT JOIN t_student s ON t.stuId = s.id left join " +
            " t_course c on t.cId = c.id ${ew.customSqlSegment}")
    List<Score> listAll(@Param("ew") QueryWrapper<Score> qw);

    @Select("SELECT c.courseName , t.xq ,MAX(t.score) maxS,MIN(t.score) minS ,AVG(t.score) avgS,COUNT(t.level='优' or null ) y," +
            "COUNT(t.level='良' or null ) l ,COUNT(t.level='中' or null ) z,COUNT(t.level='差' or null ) c FROM t_score t " +
            "LEFT JOIN  t_course c ON t.cId = c.id group by c.id ,t.xq ${ ew.customSqlSegment}")
    List<Map<String, Object>> listTotal(@Param("ew") QueryWrapper<Score> qw);
}
